package com.excel;

import java.io.File;
import java.io.IOException;
import java.sql.SQLException;
import java.util.List;
import java.util.Locale;

import jxl.CellView;
import jxl.Workbook;
import jxl.WorkbookSettings;
import jxl.biff.EmptyCell;
import jxl.format.Alignment;
import jxl.format.BorderLineStyle;
import jxl.format.Colour;
import jxl.read.biff.BiffException;
import jxl.write.Border;
import jxl.write.Label;
import jxl.write.NumberFormats;
import jxl.write.VerticalAlignment;
import jxl.write.WritableCell;
import jxl.write.WritableCellFormat;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;
import jxl.write.WriteException;
import jxl.write.biff.RowsExceededException;

public abstract class ExcelWriter implements ExcelWriteIFace {

	WorkbookSettings ws = new WorkbookSettings();
	WritableWorkbook workbook = null;
	WritableSheet s;
	/**
	 * 条件
	 */
	protected String condition = "";
	protected int dataRowCount = 0;
	/**
	 * 如果文件不存在，创建； 否则覆盖目标文件
	 */
	public static final int FILE_CREATE_OR_WRITE = 0;
	/**
	 * 如果文件不存在，创建文件，否则读取
	 */
	public static final int FILE_CREATE_OR_READ = 1;

	/**
	 * 如果Sheet不存在，创建，否则覆盖
	 */
	public static final int SHEET_CREATE_OR_WRITE = 0;
	/**
	 * 如果文件不存在，创建文件，否则读取
	 */
	public static final int SHEET_CREATE_OR_READ = 1;

	/**
	 * 文件的操作模式
	 */
	public int fileMode = FILE_CREATE_OR_WRITE;
	/**
	 * sheet操作模式
	 */
	public int sheetMode = SHEET_CREATE_OR_WRITE;

	/**
	 * 表格名
	 */
	protected String tableDisplayName;
	protected String sheetName;
	protected String fileName;
	/**
	 * 表格在Excel中的Y方向的坐标
	 */
	protected int tableYIndex = -1;
	/**
	 * 表格在Excel中的X方向的坐标
	 */
	protected int tableXIndex = -1;

	protected MyTable table;

	/**
	 * 表格之间的间隙行数
	 */
	public static int LINE_GAP = 2;
	protected MergedColumns mergedColumns;

	/**
	 * 表头设置信息
	 * 
	 * @see MergedColumns
	 */
	// private String formatStr;
	public ExcelWriter(String fileName) {
		this.fileName = fileName;
	}

	/**
	 * 设置操作模式
	 * 
	 * @throws SQLException
	 */
	public void setMode(int fileMode, int sheetMode) {
		this.fileMode = fileMode;
		this.sheetMode = sheetMode;
	}

	public void init() throws SQLException {

		ws.setLocale(new Locale("en", "EN"));
		//
		try {
			if (sheetName == null || sheetName.equals("")) {
				sheetName = "Sheet1";
			}

			File file = new File(fileName);
			// FILE_CREATE_OR_READ模式
			if (this.fileMode == this.FILE_CREATE_OR_READ) {
				if (file.exists()) {
					try {
						workbook = Workbook.createWorkbook(file, Workbook
								.getWorkbook(file));
						// SHEET_CREATE_OR_WRITE
						if (this.sheetMode == this.SHEET_CREATE_OR_WRITE) {
							s = workbook.getSheet(sheetName);
							if (s != null) {
								int sheetIndex = getSheetIndex(workbook,
										sheetName);
								workbook.removeSheet(sheetIndex);
							}
							s = workbook.createSheet(sheetName, workbook
									.getNumberOfSheets());
						} else
						// SHEET_CREATE_OR_READ
						{
							s = workbook.getSheet(sheetName);
							if (s == null) {
								s = workbook.createSheet(sheetName, workbook
										.getNumberOfSheets());
							}
						}

					} catch (BiffException e) {
						e.printStackTrace();
					}
				} else {
					workbook = Workbook.createWorkbook(file, ws);
					s = workbook.createSheet(sheetName, 0);
				}
			} else
			// FILE_CREATE_OR_WRITE模式
			{
				workbook = Workbook.createWorkbook(file, ws);
				if (sheetName == null || sheetName.equals("")) {
					sheetName = "Sheet1";
				}
				s = workbook.createSheet(sheetName, 0);
			}
		} catch (IOException e) {
			e.printStackTrace();
		}
		initConfig();
	}

	public abstract void initConfig();

	public int getSheetIndex(WritableWorkbook workbook, String sheetName) {

		int index = 0;
		s = workbook.getSheet(sheetName);

		String names[] = workbook.getSheetNames();

		for (int i = 0; i < names.length; i++) {
			if (names[i] == sheetName) {
				index = i;
				break;
			}
		}
		return index;
	}

	public void setPosition(int x, int y) {
		this.tableXIndex = x;
		this.tableYIndex = y;
	}

	public void writeExcel() throws WriteException, IOException {
		try {
			if (this.tableXIndex == -1) {
				this.tableXIndex = 0;
			}

			if (this.tableYIndex == -1) {
				if(this.s.getRows()==0) {					
					this.tableYIndex = 1;
				}else {
					this.tableYIndex = this.s.getRows() + LINE_GAP;				
				}
				
				
			}
			// 初始化Excel

			// 1.0取得字段名
			// 1.1写表头数据
			writeTableName();
			try {
				writeTableHead(mergedColumns);
			} catch (WriteException e) {
				e.printStackTrace();
			}
			// 1.2将数据导入excel
			writeDataSheet();

			// 1.3设置边框样式
			drawRect(BorderLineStyle.THIN, Colour.BLACK, null);
			// 1.4关闭excel
			exit();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

	/**
	 * 必须在操作结束时调用
	 * 
	 * @throws IOException
	 * @throws WriteException
	 */
	public void exit() throws IOException, WriteException {
		workbook.write();
		workbook.close();
	}

	protected void drawRect(BorderLineStyle style, Colour BorderColor,
			Colour bgColor) throws WriteException {
		WritableSheet sheet = this.s;
		int x = this.tableXIndex;
		int y = this.tableYIndex + 1;
		int width = getMergedColumns().getAllFieldsList().size();
		// 表头边框
		int headWidth = getMergedColumns().list.size();
		int height = dataRowCount + 2;
		for (int j = 0; j <= headWidth; j++) {
			WritableCellFormat alignStyle = new WritableCellFormat(); // 单元格样式
			alignStyle.setAlignment(Alignment.CENTRE); // 设置对齐方式
			alignStyle.setVerticalAlignment(VerticalAlignment.CENTRE);// 设置对齐方式
			alignStyle.setBorder(Border.TOP, style, BorderColor);// 设置边框的颜色和样式
			alignStyle.setBorder(Border.LEFT, style, BorderColor);// 设置边框的颜色和样式
			alignStyle.setBorder(Border.RIGHT, style, BorderColor);// 设置边框的颜色和样式
			alignStyle.setBorder(Border.BOTTOM, style, BorderColor);// 设置边框的颜色和样式
			if (bgColor != null)
				alignStyle.setBackground(bgColor); // 背静色
			WritableCell writableCell = this.s.getWritableCell(x + j,
					this.tableYIndex);
			if (writableCell instanceof EmptyCell) {
				Label l = new Label(x + j, this.tableYIndex, "", alignStyle);
				s.addCell(l);
			} else {
				writableCell.setCellFormat(alignStyle);
			}

		}

		for (int w = 0; w < width; w++) {
			for (int h = 0; h < height; h++) {
				WritableCellFormat alignStyle = new WritableCellFormat(); // 单元格样式
				alignStyle.setAlignment(Alignment.CENTRE); // 设置对齐方式
				alignStyle.setVerticalAlignment(VerticalAlignment.CENTRE);// 设置对齐方式
				alignStyle.setBorder(Border.TOP, style, BorderColor);// 设置边框的颜色和样式
				alignStyle.setBorder(Border.LEFT, style, BorderColor);// 设置边框的颜色和样式
				alignStyle.setBorder(Border.RIGHT, style, BorderColor);// 设置边框的颜色和样式
				alignStyle.setBorder(Border.BOTTOM, style, BorderColor);// 设置边框的颜色和样式
				if (bgColor != null)
					alignStyle.setBackground(bgColor); // 背静色
				WritableCell writableCell = this.s.getWritableCell(x, y);
				writableCell.setCellFormat(alignStyle);
				y++;
			}
			y -= height;
			x++;
		}

		for (int w = 0; w < width; w++) {
			CellView cf = new CellView();
			// cf.setAutosize(true);
			s.setColumnView(x + w, cf);
		}
	}

	/**
	 * 表头 初始位置设在(1,0)
	 * 
	 * @param s
	 * @throws WriteException
	 * @throws SQLException
	 */
	protected void writeTableName() {
		// TODO 控制表名与数据的间隙 tableYIndex - 1
		tableYIndex = tableYIndex + 2;
		Label l = new Label(tableXIndex, tableYIndex - 1, this.tableDisplayName);
		try {
			if (!this.condition.equals("")) {
				Label conditionLabel = new Label(tableXIndex, 1, this.condition);
				s.addCell(conditionLabel);
			}

			s.addCell(l);
		} catch (RowsExceededException e) {

			e.printStackTrace();
		} catch (WriteException e) {

			e.printStackTrace();
		}
	}

	protected void writeTableHead(MergedColumns mergedColumns)
			throws WriteException, SQLException {
		List<ExcelColumn> list = mergedColumns.list;
		int curColIndex = tableXIndex;
		for (int i = 0; i < list.size(); i++) {
			ExcelColumn excelColumn = list.get(i);
			if (excelColumn.isComplex()) {
				int colLen = excelColumn.getColumnNames().size();

				List<ExcelColumn> excelColumns = excelColumn.getColumns();
				for (int j = 0; j < excelColumns.size(); j++) {
					ExcelColumn aExcelColumn = excelColumns.get(j);
					Label label = new Label(curColIndex + j, tableYIndex + 1,
							aExcelColumn.getDisplayName());
					s.addCell(label);

				}
				WritableCellFormat cf2 = new WritableCellFormat(
						NumberFormats.INTEGER);
				cf2.setAlignment(Alignment.CENTRE);
				Label label = new Label(curColIndex, tableYIndex, excelColumn
						.getDisplayName(), cf2);
				s.addCell(label);
				s.mergeCells(curColIndex, tableYIndex,
						curColIndex + colLen - 1, tableYIndex);
				curColIndex += colLen;
			} else {
				String displayNameString = excelColumn.getDisplayName();
				Label l = new Label(curColIndex, tableYIndex + 1,
						displayNameString);
				s.addCell(l);
				curColIndex++;

			}
		}

	}

	/**
	 * 起始位置从(2,0)开始
	 * 
	 * @param resultSet
	 * @throws WriteException
	 * @throws SQLException
	 */
	protected abstract void writeDataSheet();

	public String getFileName() {
		return fileName;
	}

	public void setFileName(String fileName) {
		this.fileName = fileName;
	}

	public MyTable getTable() {
		return table;
	}

	public void setTable(MyTable table) {
		this.table = table;
	}

	public MergedColumns getMergedColumns() {
		return mergedColumns;
	}

	public void setMergedColumns(MergedColumns mergedColumns) {
		this.mergedColumns = mergedColumns;
	}

	public String getTableDisplayName() {
		return tableDisplayName;
	}

	public void setTableDisplayName(String tableDisplayName) {
		this.tableDisplayName = tableDisplayName;
	}

	public String getSheetName() {
		return sheetName;
	}

	public void setSheetName(String sheetName) {
		this.sheetName = sheetName;
	}

	public String getCondition() {
		return condition;
	}

	public void setCondition(String condition) {
		this.condition = condition;
	}

}
